package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import Clases.domain.Usuario;

public class JDBCUsuarioDAO implements IUsuarioDAO {

	public List selectAllUsuariosZona(Connection conn, Integer zonaOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE Z.zonaOID=U.zonaOID and U.zonaOID = '"
					+ zonaOID + "'";

			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				u.setBarrio(result.getString("barrio"));
				u.setZona(result.getString("nombre"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	public List selectAllUsuariosManifestacion(Connection conn,	int manifestacionOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT* FROM Usuario U, Usuariomanifestacion UM, Manifestacion M WHERE U.usuarioOID = UM.usuarioOID and M.manifestacionOID = UM.manifestacionOID AND M.manifestacionOID LIKE '"
					+ manifestacionOID + "'";
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {

			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}

	public List selectAllUsuarios() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE Z.zonaOID=U.zonaOID ";
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();

			while (result.next()) {
				Usuario temp = new Usuario();
				temp.setEmail(result.getString("email"));
				temp.setNick(result.getString("nick"));
				temp.setPassword(result.getString("password"));
				temp.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					temp.setRecicla(true);
				} else {
					temp.setRecicla(false);
				}

				temp.setAceite(result.getInt("aceite"));
				temp.setMedicamentos(result.getInt("medicina"));
				temp.setOrganico(result.getInt("organico"));
				temp.setPapel(result.getInt("papel"));
				temp.setPilas(result.getInt("pilas"));
				temp.setPlastico(result.getInt("plastico"));
				temp.setVidrio(result.getInt("vidrio"));
				temp.setBarrio(result.getString("barrio"));
				temp.setZona(result.getString("nombre"));
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}

	public Usuario selectUsuario(String userForm, String passwdForm) {

		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario usuario = new Usuario();

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE Z.zonaOID=U.zonaOID and U.nick LIKE '"
					+ userForm + "' AND U.password LIKE  '" + passwdForm + "'";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				usuario.setEmail(result.getString("email"));
				usuario.setNick(result.getString("nick"));
				usuario.setPassword(result.getString("password"));
				usuario.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					usuario.setRecicla(true);
				} else {
					usuario.setRecicla(false);
				}

				usuario.setAceite(result.getInt("aceite"));
				usuario.setMedicamentos(result.getInt("medicina"));
				usuario.setOrganico(result.getInt("organico"));
				usuario.setPapel(result.getInt("papel"));
				usuario.setPilas(result.getInt("pilas"));
				usuario.setPlastico(result.getInt("plastico"));
				usuario.setVidrio(result.getInt("vidrio"));
				usuario.setBarrio(result.getString("barrio"));
				usuario.setZona(result.getString("nombre"));

			} else {
				usuario = null;
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return usuario;
	}

	public void insertUsuario(String nick, String email, String password, String zona, String barrio, int aceite, int basuraGenerada, int medicamentos, String motivo, int organico, int papel, int pilas, int plastico, boolean recicla, int vidrio){
		
		Connection conn=ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;

		int rec;
		if(recicla){
			rec=1;
		}
		else{
			rec=0;
		}
		int num=(new JDBCUsuarioDAO()).contarUsuarios();
		int zonaNum=(new JDBCUsuarioDAO()).devuelveZonaInt(zona);
        String sql = "INSERT INTO Usuario ( usuarioOID, zonaOID, password, email, nick, basuraGenerada, recicla, vidrio, papel, plastico, organico, aceite, medicina, pilas, barrio, motivo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, num);
            stmt.setInt(2, zonaNum);
            stmt.setString(3, password);
            stmt.setString(4, email);
            stmt.setString(5, nick);
            stmt.setInt(6, basuraGenerada);
            stmt.setInt(7, rec);
            stmt.setInt(8, vidrio);
            stmt.setInt(9, papel);
            stmt.setInt(10, plastico);
            stmt.setInt(11, organico);
            stmt.setInt(12, aceite);
            stmt.setInt(13, medicamentos);
            stmt.setInt(14, pilas);
            stmt.setString(15, barrio);
            stmt.setString(16, motivo);
            

			stmt.executeUpdate();


		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}

	public int contarUsuariosNo() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		int res = 0;

		try {

			String sql = "SELECT count(*) FROM Usuario WHERE recicla=0";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				res = result.getInt("count(*)");

			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return res;
	}

	public int contarUsuariosNoTipo(String motivo) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		int r = 0;

		try {

			String sql = "SELECT count(*) FROM Usuario WHERE recicla=0 AND motivo LIKE '"
					+ motivo + "'";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				r = result.getInt("count(*)");

			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return r;
	}

	public List selectAllUsuariosZon(Connection conn, Integer zonaOID) {

		PreparedStatement stmt = null;
		List searchResults = new LinkedList();
		ResultSet result = null;

		try {

			String sql = "SELECT email,nick,nombre,password,basuraGenerada,recicla,aceite,medicina,organico,papel,pilas,plastico,vidrio,barrio FROM Usuario U,Zona Z WHERE z.zonaOID=U.zonaOID and U.zonaOID = '"
					+ zonaOID + "'";

			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Usuario u = new Usuario();
				u.setEmail(result.getString("email"));
				u.setNick(result.getString("nick"));
				u.setPassword(result.getString("password"));
				u.setBasuraGenerada(result.getInt("basuraGenerada"));
				if (result.getInt("recicla") == 1) {
					u.setRecicla(true);
				} else {
					u.setRecicla(false);
				}

				u.setAceite(result.getInt("aceite"));
				u.setMedicamentos(result.getInt("medicina"));
				u.setOrganico(result.getInt("organico"));
				u.setPapel(result.getInt("papel"));
				u.setPilas(result.getInt("pilas"));
				u.setPlastico(result.getInt("plastico"));
				u.setVidrio(result.getInt("vidrio"));
				u.setBarrio(result.getString("barrio"));
				u.setZona(result.getString("nombre"));
				searchResults.add(u);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;

	}
	
	public int contarUsuarios(){
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		int enc=0;

		try {
			
			String sql = "SELECT count(*) FROM Usuario ";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				enc=result.getInt("count(*)");

			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return enc;
	}
	
	public int devuelveZonaInt(String zona){
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		ResultSet result = null;
		int encontrado=0;

		try {
			
			String sql = "SELECT zonaOID FROM Zona where nombre LIKE '"+zona+"' ";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			if (result.next()) {

				encontrado=result.getInt("zonaOID");

			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return encontrado;
	}
}
